﻿Imports System.Data.Linq
Imports System.Linq

Public Class MBuyingRate
    Private db As New TanNamChinhDataContext
   
    'Lấy thông tin File chưa ký hợp đồng

    Public Function getContList(ByVal idFile As Integer) As IQueryable
        Try
            Dim query = From b In db.tblConts
                        Where b.tblHBL.tblFileMBL.ID_File = idFile
                        Select b.tblContType.ID_ContType


            'Return (From r In db.tblCurrencies
            '           Select r).Single
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function getFileFeeList(ByVal idFile As Integer) As IQueryable
        Try
           
            ''Lấy các phí đã có trong filefee
            'Dim listFee = From b In db.tblFileFees
            '              Where b.tblFile.ID_File = idFile And b.IsBuying = True
            '              Select b.ID_Fee

       
                'Lấy các phí mới, có trong fee, chưa có trong filefee
            Dim listnewFee = From c In db.tblFees
                             Where c.ID_FeeType = 1 And c.IsActive = True And Not (From b In db.tblFileFees
                                                              Where b.tblFile.ID_File = idFile And Not b.ID_AdvanceVoucher.HasValue
                                                              Select b.ID_Fee).Contains(c.ID_Fee)
                            Select c.ID_Fee

            'Thêm phí mới vào filefee rồi load lại
            'Kiểm tra lại xem phí này có phải được thêm vào bảng cước kia hay không
            'Tính lại toàn bộ phí, kiểm tra xem có phí nào bên giá cước có nhập thêm mà bên phí chưa có thì update lại
            Dim isExport As Boolean

            isExport = IsExportFile(idFile)
            Dim mmbl As New MMBL
            Dim query
            'Lấy id booking và voyage
            Dim _id_mbl = mmbl.getIDMBL(idFile)
            Dim _id_voyage = mmbl.getIDVoyage(_id_mbl)
            If isExport = True Then
                query = getBuyingFollowCont(idFile, _id_mbl, _id_voyage).ToList()
                If listnewFee IsNot Nothing Then
                    'Add phí vào
                    For Each idFee As Integer In listnewFee
                        For Each b As GetBuyingRateOfFileResult In query

                            If b.ID_Fee = idFee And b.Price > 0 Then
                                Dim obj As New tblFileFee
                                obj.ID_Fee = idFee
                                obj.ID_File = idFile
                                obj.UnitPrice = b.UnitPrice
                                obj.Quantity = b.Quantity
                                obj.ID_Currency = b.ID_Currency
                                obj.Price = b.Price
                                obj.Description = DescriptionContOfFile(idFile, _id_mbl, _id_voyage, idFee)


                                InsertNewRecord(obj)
                                Exit For
                            End If

                         
                        Next
                        
                    Next

                End If
            Else
                'Nếu là hàng nhập thì kiểm tra hàng lẻ

                If IsLCLFile(idFile) = False Then
                    'Nếu đúng là hàng lẻ thì tính theo 

                    query = getBuyingImportFollowCont(idFile, _id_mbl, _id_voyage).ToList()
                    If listnewFee IsNot Nothing Then
                        'Add phí vào
                        For Each idFee As Integer In listnewFee
                            For Each b As GetBuyingRateOfFileImportResult In query

                                If b.ID_Fee = idFee And b.Price > 0 Then
                                    Dim obj As New tblFileFee
                                    obj.ID_Fee = idFee
                                    obj.ID_File = idFile
                                    obj.UnitPrice = b.UnitPrice
                                    obj.Quantity = b.Quantity
                                    obj.ID_Currency = b.ID_Currency
                                    obj.Price = b.Price

                                    obj.Description = DescriptionContOfFile(idFile, _id_mbl, _id_voyage, idFee)


                                    InsertNewRecord(obj)
                                    Exit For
                                End If


                            Next

                        Next

                    End If
                Else
                    query = getBuyingFollowCont(idFile, _id_mbl, _id_voyage).ToList()
                    If listnewFee IsNot Nothing Then
                        'Add phí vào
                        For Each idFee As Integer In listnewFee
                            For Each b As GetBuyingRateOfFileResult In query

                                If b.ID_Fee = idFee And b.Price > 0 Then
                                    Dim obj As New tblFileFee
                                    obj.ID_Fee = idFee
                                    obj.ID_File = idFile
                                    obj.UnitPrice = b.UnitPrice
                                    obj.Quantity = b.Quantity
                                    obj.ID_Currency = b.ID_Currency
                                    obj.Price = b.Price
                                    obj.Description = DescriptionContOfFile(idFile, _id_mbl, _id_voyage, idFee)


                                    InsertNewRecord(obj)
                                    Exit For
                                End If


                            Next

                        Next

                    End If
                End If
            End If

            '---------------------------------------------------------

           


            'Lấy các thông tin fee buying theo từng file
            Dim listCont = From b In db.tblFileFees
                        Where b.tblFile.ID_File = idFile And Not b.ID_AdvanceVoucher.HasValue And b.tblFee.IsActive = True
                        Order By b.tblFee.Rank Ascending
                        Select New With {b.ID_FileFee,
                                         b.ID_Fee,
                                         b.tblFee.FeeName,
                                         b.UnitPrice,
                                         b.Quantity,
                                         b.Price,
                                         b.InvoiceNo,
                                         .Description = b.Description,
                                         b.ID_Currency}
            'Dim listBill = From b In db.tblFileFees
            '        Where b.tblFee.IsBill = True And b.tblFile.ID_File = idFile And Not b.ID_AdvanceVoucher.HasValue And b.tblFee.FeeName <> "OTHER"
            '        Order By b.tblFee.ID_FeeType, b.tblFee.FeeName Ascending
            '        Select New With {b.ID_FileFee, b.ID_Fee, b.tblFee.FeeName, b.UnitPrice, b.Quantity, b.Price, b.InvoiceNo, .Description = b.Description, b.ID_Currency}

            'Dim arow = From b In db.tblFileFees
            'Where(b.tblFile.ID_File = idFile And Not b.ID_AdvanceVoucher.HasValue And b.tblFee.FeeName = "OTHER")
            '                        Select New With {b.ID_FileFee, b.ID_Fee, b.tblFee.FeeName, b.UnitPrice, b.Quantity, b.Price, b.InvoiceNo, .Description = b.Description, b.ID_Currency}


            'Return listCont.Concat(listBill).Concat(arow)

          
            Return listCont
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function DescriptionContOfFile(ByVal id_file As Integer, ByVal id_mbl As Integer, ByVal id_voy As Integer, ByVal id_Fee As Integer) As String
        Try

     
        'Lấy thông tin cont và group
        'Dim query = (From a In db.tblMBLs
        '            Join b In db.tblFileMBLs On b.ID_MBL Equals a.ID_MBL
        '            Join c In db.tblConts On c.ID_File Equals b.ID_File
        '            Join e In db.tblFreightTariffs On e.ID_Voyage Equals a.ID_Voyage
        '            Join f In db.tblContTypes On f.ID_ContType Equals c.ID_ContType And f.ID_ContType Equals e.ID_ContType
        '            Where b.ID_File = id_file And c.ID_File = id_file And a.ID_MBL = id_mbl And b.ID_MBL = id_mbl And e.ID_Voyage = id_voy And a.ID_Voyage = id_voy And e.IsBuying = True And Not (From aa In db.tblFees
        '                                                                                                                                                                                       Where aa.ID_FeeType = 1 And aa.IsBill = 0
        '                                                                                                                                                                                       Select aa.ID_Fee).Contains(e.ID_Fee)
        '            Select c).Distinct
        'Kiểm tra xem phí đang xét có phải là phí bill không
        'nếu đúng thì trả về rỗng
        Dim isBills As Boolean = (From a In db.tblFees Where a.ID_Fee = id_Fee Select a.IsBill).First()

        If isBills = True Then
            Return ""
        Else

            Dim str As String = ""
                Dim groups = From a In db.tblConts
                             Where a.ID_File = id_file
                              Group By IDContType = a.ID_ContType, ContTypeName = a.tblContType.ContTypeName
                              Into myGroup = Group, Count()
                               Select IDContType, ContTypeName

            For Each g In groups
                    Dim query = (From a In db.tblConts
                           Where a.ID_File = id_file And a.ID_ContType = g.IDContType
                           Select a).ToList()
                'Lấy ra giá tiền của cont dựa vào bảng taff
                Dim prices = (From b In db.tblFreightTariffs
                             Where b.ID_Voyage = id_voy And b.ID_Fee = id_Fee And b.ID_ContType = g.IDContType
                             Select b.Price).First()

                str &= g.ContTypeName & ":" & query.Count & "*" & prices.ToString() & " ;"
            Next
            str = str.Trim(";")
            Return str
        End If

        'Duyệt qua để lấy thông tin
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    ''' <summary>
    ''' Trả về True nếu là hàng xuất, False là hàng nhập
    ''' </summary>
    ''' <param name="idFile"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsExportFile(ByVal idFile As Integer) As Boolean
        Dim query = (From a In db.tblFiles
                     Where a.ID_File = idFile
                     Select a.IsExport).First()
        Return query
    End Function
    ''' <summary>
    ''' True nếu hàng FULL, False là hàng lẻ
    ''' </summary>
    ''' <param name="idFile"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsLCLFile(ByVal idFile As Integer) As Boolean
        Dim query = (From a In db.tblFiles
                     Where a.ID_File = idFile And a.IsExport = False
                     Select a.IsFCL).First()
        Return query
    End Function
    ''' <summary>
    ''' Kiểm tra có tồn tại bảng giá cước của cont theo File chưa
    ''' </summary>
    ''' <param name="idFile"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExistCuocCont(ByVal idFile As Integer) As Boolean
        'Lấy danh sách loại cont của File
        Dim listCont = (From a In db.tblConts
                       Where a.ID_File = idFile
                       Select a).Distinct
        'Lấy danh sách loại cont trong bảng giá cước
        Dim listCuocCont = From b In db.tblFileMBLs Join a In db.tblFreightTariffs On b.tblMBL.ID_Voyage Equals a.ID_Voyage
                           Where b.ID_File = idFile
                            Select a.ID_ContType


        For Each obj As tblCont In listCont

            If Not listCuocCont.Contains(obj.ID_ContType) Then
                Return False
            End If
        Next
        Return True
    End Function
    ''' <summary>
    ''' Kiểm tra xem File đã được tính phí giá mua hay chưa
    ''' </summary>
    ''' <param name="idFile"></param>
    ''' <param name="idFeeType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExistFileFee(ByVal idFile As Integer, ByVal idFeeType As Integer) As Boolean
        Dim numberFileFee As Integer
        numberFileFee = (From b In db.tblFileFees
                Where b.ID_File = idFile And b.tblFee.tblFeeType.ID_FeeType = idFeeType
                Select b).Count
        Dim Fee As Integer
        Fee = (From b In db.tblFees
               Where b.ID_FeeType = idFeeType
               Select b).Count()

        If numberFileFee > 0 Then
            Return True
        Else
            Return False

        End If

    End Function

    Public Function getBuyingFollowCont(ByVal idFile As Integer, ByVal idmbl As Integer, ByVal idVoyage As Integer) As ISingleResult(Of TNCDAL.GetBuyingRateOfFileResult)
        'Kiểm tra hàng nhập hay hàng xuất

        Dim query = db.GetBuyingRateOfFile(idFile, idVoyage, idmbl, 1)


        '    var query =
        'from c in dc.Customers
        'where !(from o in dc.Orders
        '        select o.CustomerID)
        '    .Contains(c.CustomerID)
        'select c;
        'Dim query2 As ISingleResult(Of TNCDAL.GetBuyingRateOfFileResult) = From f In db.tblFees
        '             Where Not (From o In query
        '                       Select o.ID_Fee).Contains(f.ID_Fee)
        '                   Select f.ID_Fee, f.FeeName, Price = 0.0

        'Dim emptyTable As tblTruck() = {New tblTruck With {.ID_Truck = 0, .TruckName = "-------"}}
        ' Dim q = (From e In query2 Select e).Union(query).ToList
        Return query
    End Function
    Public Function getBuyingImportFollowCont(ByVal idFile As Integer, ByVal idmbl As Integer, ByVal idVoyage As Integer) As ISingleResult(Of TNCDAL.GetBuyingRateOfFileImportResult)
        'Kiểm tra hàng nhập hay hàng xuất

        Dim query = db.GetBuyingRateOfFileImport(idFile, idVoyage, idmbl, 1)


        '    var query =
        'from c in dc.Customers
        'where !(from o in dc.Orders
        '        select o.CustomerID)
        '    .Contains(c.CustomerID)
        'select c;
        'Dim query2 As ISingleResult(Of TNCDAL.GetBuyingRateOfFileResult) = From f In db.tblFees
        '             Where Not (From o In query
        '                       Select o.ID_Fee).Contains(f.ID_Fee)
        '                   Select f.ID_Fee, f.FeeName, Price = 0.0

        'Dim emptyTable As tblTruck() = {New tblTruck With {.ID_Truck = 0, .TruckName = "-------"}}
        ' Dim q = (From e In query2 Select e).Union(query).ToList
        Return query
    End Function
    Public Sub InsertNewRecord(ByVal obj As tblFileFee)
        'db.tblCurrencies.InsertOnSubmit(obj)
        db.GetTable(Of tblFileFee)().InsertOnSubmit(obj)
        db.SubmitChanges()
    End Sub
    Public Sub InsertNewRecord(ByVal obj As tblContFee)
        'db.tblCurrencies.InsertOnSubmit(obj)
        db.GetTable(Of tblContFee)().InsertOnSubmit(obj)
        db.SubmitChanges()
    End Sub

    Public Sub UpdateRecord(ByVal obj As tblFileFee)
        Dim getData = (From b In db.tblFileFees
                                Where b.ID_FileFee = obj.ID_FileFee
                                Select b).Single()
        getData.ID_File = obj.ID_File
        getData.ID_Fee = obj.ID_Fee
        getData.Price = obj.Price
        getData.InvoiceNo = obj.InvoiceNo
        getData.UnitPrice = obj.UnitPrice
        getData.Quantity = obj.Quantity
        getData.Description = obj.Description
        getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
        getData.ID_PaymentVoucher = obj.ID_PaymentVoucher

        getData.ID_Currency = obj.ID_Currency
        db.SubmitChanges()

    End Sub
    Public Sub UpdatePrice(ByVal obj As tblFileFee)
        Dim getData = (From b In db.tblFileFees
                                Where b.ID_File = obj.ID_File And b.ID_Fee = obj.ID_Fee And b.ID_AdvanceVoucher = b.ID_AdvanceVoucher
                                Select b).Single()
        getData.ID_File = obj.ID_File
        getData.ID_Fee = obj.ID_Fee
        getData.Price = obj.Price
        getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
        db.SubmitChanges()

    End Sub
    Public Sub UpdatePrice2(ByVal obj As tblFileFee)
        Dim getData = (From b In db.tblFileFees
                                Where b.ID_FileFee = obj.ID_FileFee
                                Select b).Single()
       
        getData.Price = obj.Price
        db.SubmitChanges()

    End Sub

    Public Sub UpdateRecord(ByVal obj As tblContFee)
        Dim getData = (From b In db.tblContFees
                                Where b.ID_ContFee = obj.ID_ContFee
                                Select b).Single()
        
        getData.Price = obj.Price


        db.SubmitChanges()

    End Sub
    Public Sub UpdatePaymentVoucher(ByVal obj As tblFileFee)
        Dim getData = (From b In db.tblFileFees
                                Where b.ID_FileFee = obj.ID_FileFee And Not (b.ID_AdvanceVoucher.HasValue)
                                 Select b).Single()
        'getData.ID_File = obj.ID_File
        'getData.ID_Fee = obj.ID_Fee
        'getData.Price = obj.Price
        'getData.InvoiceNo = obj.InvoiceNo
        'getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
        getData.ID_PaymentVoucher = obj.ID_PaymentVoucher
        'getData.ID_ReceiptVoucher = obj.ID_ReceiptVoucher

        db.SubmitChanges()

    End Sub
    Public Sub UpdateAdvanceVoucher(ByVal obj As tblFileFee)
        Dim getData = (From b In db.tblFileFees
                                Where b.ID_FileFee = obj.ID_FileFee
                                Select b).Single()
        'getData.ID_File = obj.ID_File
        'getData.ID_Fee = obj.ID_Fee
        'getData.Price = obj.Price
        'getData.InvoiceNo = obj.InvoiceNo
        'getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
        getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
        'getData.ID_ReceiptVoucher = obj.ID_ReceiptVoucher

        db.SubmitChanges()

    End Sub
    Public Sub DeleteRecord(ByVal id As Integer)
        Dim getData = (From b In db.tblFiles
                                Where b.ID_File = id
                                Select b).Single()
        db.tblFiles.DeleteOnSubmit(getData)
        db.SubmitChanges()
    End Sub
    Public Sub DeleteRecordHasAdvance(ByVal idA As Integer)
        Dim getData = From b In db.tblFileFees
                                Where b.ID_AdvanceVoucher = idA
                                Select b

        For Each b As tblFileFee In getData
            db.tblFileFees.DeleteOnSubmit(b)

            'getData.ID_ReceiptVoucher = obj.ID_ReceiptVoucher
            db.SubmitChanges()
        Next
        
    End Sub
    Public Sub DeleteRecordOfAdvance(ByVal idA As Integer, ByVal idFile As Integer, ByVal idFee As Integer)
        Dim getData = From b In db.tblFileFees
                                Where b.ID_AdvanceVoucher = idA And b.ID_Fee = idFee And b.ID_File = idFile
                                Select b

        For Each b As tblFileFee In getData
            db.tblFileFees.DeleteOnSubmit(b)

            'getData.ID_ReceiptVoucher = obj.ID_ReceiptVoucher
            db.SubmitChanges()
        Next

    End Sub
    Public Sub DeleteRecordHasPayment(ByVal idP As Integer)
        Dim getData = From b In db.tblFileFees
                                Where b.ID_PaymentVoucher = idP
                                Select b

        For Each b As tblFileFee In getData
            b.ID_PaymentVoucher = Nothing
            'getData.ID_ReceiptVoucher = obj.ID_ReceiptVoucher
            db.SubmitChanges()
        Next

    End Sub
    Public Sub DeleteBuyingFee(ByVal idfile As Integer)
        Try
            'Lấy id payment đầu ti
            Dim idPaymentVoucher = (From b In db.tblFileFees
                             Where b.ID_File = idfile
                             Select b.ID_PaymentVoucher).First()
            'Lấy danh sách các file cần xóa
            Dim deleteRelatedRecords =
                From relatedRecords In db.tblFileFees
                Where relatedRecords.ID_File = idfile
                Select relatedRecords
            'Duyệt qua mỗi file cần xóa
            For Each obj In deleteRelatedRecords
                db.tblFileFees.DeleteOnSubmit(obj)
                db.SubmitChanges()
            Next
            'Xóa luôn đầu phiếu chi tương ứng



            If idPaymentVoucher IsNot Nothing Then

                Dim pc = (From pv In db.tblPaymentVouchers
                         Where pv.ID_PaymentVoucher = idPaymentVoucher
                         Select pv).Single

                If pc IsNot Nothing Then
                    db.tblPaymentVouchers.DeleteOnSubmit(pc)
                    db.SubmitChanges()
                End If
            End If
        Catch ex As Exception
            Throw ex
        End Try
     


    End Sub
    Public Function getNumberofCont(ByVal idFile As Integer) As Integer
        Dim num As Integer
        Try
            num = (From b In db.tblConts
                      Where b.tblHBL.tblFileMBL.ID_File = idFile
                        Select b
                       ).Count
        Catch ex As Exception
            Throw ex
        End Try
        Return num
    End Function
    Public Function getPriceFollowFee(ByVal idFile As Integer, ByVal idFee As Integer) As Double
        Dim nums As Double
        Try
            nums = (From b In db.tblSellingFees
                   Where b.tblHBL.tblFileMBL.ID_File = idFile And b.ID_Fee = idFee
                   Select b.Price).First()
        Catch ex As Exception
            Return 0
        End Try
        Return nums
    End Function
    Public Function getPriceFollowFee(ByVal idFileFee As Integer) As Double
        Dim nums As Double
        Try
            nums = (From b In db.tblFileFees
                   Where b.ID_FileFee = idFileFee
                   Select b.Price).First()
        Catch ex As Exception
            Return 0
        End Try
        Return nums
    End Function
    ''' <summary>
    ''' Kiểm tra xem phí đã được tạm ứng chưa
    '''  Trả về true là có tồn tại, false là chưa có
    ''' </summary>
    ''' <param name="id_filecont"></param>
    ''' <param name="id_fee"></param>
    ''' <returns> </returns>
    ''' <remarks></remarks>

    Public Function IsExistFee(ByVal id_filecont As Integer, ByVal id_fee As Integer) As Boolean
        Dim query As IQueryable


        'Nếu đang xét filefee

        query = From a In db.tblFileFees
                Where a.ID_Fee = id_fee And a.ID_File = id_filecont And a.ID_AdvanceVoucher IsNot Nothing
                Select a



        Dim count As Integer = 0
        For Each obj As tblFileFee In query
            count += 1
        Next

        If count <> 0 Then
            Return True
        Else
            Return False
        End If




    End Function
    Public Function IsPayment(ByVal idFile As Integer) As Boolean
        Try
            Dim q = (From a In db.tblFileFees
                           Where a.ID_File = idFile
                           Select a.ID_PaymentVoucher).First

            If q IsNot Nothing Then
                'có chi rồi
                Return True
            Else : Return False
            End If
        Catch ex As Exception
            Throw ex
        End Try
       
    

    End Function
    Public Function GetIDFileFeeOfFile(ByVal idFee As Integer, ByVal idFile As Integer) As Integer
        Dim id As Integer
        id = (From q In db.tblFileFees
              Where q.ID_Fee = idFee And q.ID_File = idFile
              Select q.ID_FileFee).First()
        Return id
    End Function
#Region "Thanh"
    Public Function GetBuyingRateList(ByVal idFile As Integer) As ISingleResult(Of sp_Thanh_GetBuyingRateResult)
        Try
            'Return db.sp_Thanh_GetBuyingRate(idFile)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
#End Region
End Class
